<?php session_start(); ?>
<!-- resultModule.php = a Module that shows Record from winestore database with some search criteria specified by searchScreen.php, -->
<!-- assigns data to smarty template, and connects to database using PDO-Driver -->
<?php
	
	//defines the database type used by PDO driver
	define('DB_TYPE', 'mysql');
	//defines smarty base directory to run smarty template
	
	define("USER_HOME_DIR", "/home/stud/s3274282");
	require(USER_HOME_DIR . "/php/Smarty-2.6.26/Smarty.class.php");
	
	$smarty->template_dir = USER_HOME_DIR . "/.HTMLinfo/WDA/PartE/templates";
	$smarty->compile_dir = USER_HOME_DIR . "/.HTMLinfo/WDA/PartE/templates_c";
	$smarty->cache_dir = USER_HOME_DIR . "/.HTMLinfo/WDA/PartE/cache";
	$smarty->config_dir = USER_HOME_DIR . "/.HTMLinfo/WDA/PartE/configs";
	
	define('DB_HOST', 'yallara.cs.rmit.edu.au');
	define('DB_PORT', '50448'); 
	define('DB_PW', 'winestorepassword'); 
	
	define('DB_NAME', 'winestore');
	define('DB_USER', 'winestoreuser'); 
	
	$smarty = new Smarty;
	$historyIndex = $_GET["historyIndex"];
	$isDisplayingHistory= $_GET["isDisplayingHistory"];
	$search_wine_name = $_GET["wineName"];
	$search_winery_name = $_GET["wineryName"];
	$search_state = $_GET["state"];
	$search_region = $_GET["Region"];
	$search_grape_variety = $_GET["grapeVariety"];
	$search_rangeFrom = $_GET["rangeFrom"];
	$search_rangeTo = $_GET["rangeTo"];
	$search_minNoStock = $_GET["minNoStock"];
	$search_minNoOrder = $_GET["minNoOrder"];
	$criteria = "<font size=2>";
	$state = "";
	$quantity = 0;
	if(isset($isDisplayingHistory) == true && $isDisplayingHistory == "true" && isset($historyIndex) == true)
	{
		if(isset($_SESSION['history']) == false)
		{
			$smarty->assign('isDisplayingHistory','error');
			$smarty->display('resultModule.tpl');
		}
		else
		{
			try
			{
				$bigArray = $_SESSION['history'];
				$data = $bigArray[$historyIndex];
				$smarty->assign('queryResult',$data);
				$smarty->assign('isDisplayingHistory','true');
				$smarty->assign('notice',generateNoticeSession());
				$smarty->assign('result',count($data));
				$smarty->assign('index',$historyIndex);
				$smarty->display('resultModule.tpl');
			}
			catch(Exception $e)
			{
				$smarty->assign('isDisplayingHistory','error');
				$smarty->display('resultModule.tpl');
			}
		}
	}
	else
	{
		

		//if the start year is bigger than the end year, prints error message
		if($search_rangeFrom > $search_rangeTo && strcmp($search_rangeFrom,"-") != 0 && strcmp($search_rangeTo,"-") != 0)
		{
			$smarty->assign('yearIsValid','false');
			$smarty->display('resultModule.tpl');
		}
		//if the Minimum number of wine in stock & Minimum number of wine ordered field input is NOT an integer, prints error message
		else if(!isInteger($search_minNoStock) && strcmp($search_minNoStock,"") != 0)
		{
			$smarty->assign('minNoStockIsValid','false');
			$smarty->display('resultModule.tpl');
		}
		else if(!isInteger($search_minNoOrder) && strcmp($search_minNoOrder,"") != 0)
		{
			$smarty->assign('minNoOrderIsValid','false');
			$smarty->display('resultModule.tpl');
		}
		// if all validation is complete, begin to process search function
		else
		{
			$base_query =
			"SELECT DISTINCT wine.wine_id, wine.wine_name, grape_variety.variety, wine.year, winery.winery_name, region.region_name, inventory.on_hand, inventory.cost
			FROM wine, grape_variety, winery, region, wine_variety, items, inventory
			WHERE wine.wine_id = wine_variety.wine_id
			AND wine_variety.variety_id = grape_variety.variety_id
			AND wine.winery_id = winery.winery_id
			AND winery.region_id = region.region_id
			AND wine.wine_id = inventory.wine_id
			AND wine.wine_id = items.wine_id ";
			
			//checks whether the criteria are inserted by the user or not, and appends to current base_query
			if(strcmp($search_wine_name,"") != 0)
			{
				$base_query .= " AND wine.wine_name like '%".$search_wine_name."%' ";
				$criteria .= "Wine Name : <i>'".$search_wine_name."'</i><br/> ";
			}
			if(strcmp($search_winery_name,"") != 0)
			{
				$base_query .= " AND winery.winery_name like '%".$search_winery_name."%' ";
				$criteria .= "Winery Name : <i>'".$search_winery_name."'</i><br/> ";
			}
			if(strcmp($search_state,"-") != 0)
			{
				$state = $search_state;
				$criteria .= "State : <i>'".$search_state."'</i><br/> ";
			}
			if(strcmp($search_state,"-") == 0 || strcmp($search_state,"ALL") == 0)
			{
				$state = "ALL";
				$criteria .= "State : <i>'".$state."'</i><br/> ";
			}
			if(strcmp($search_region,"-") != 0 && strcmp($search_region,"1") != 0)
			{
				$base_query .= "AND region.region_id = '".$search_region."' ";
				$criteria .= "Region : <i>'".$search_region."'</i><br/> ";
			}
			if(strcmp($search_grape_variety,"-") != 0 && strcmp($search_grape_variety,"ALL") != 0)
			{
				$base_query .= "AND grape_variety.variety_id = '".$search_grape_variety."' ";
				$criteria .= "Grape Variety : <i>'".$search_grape_variety."'</i><br/> ";
			}
			if(strcmp($search_rangeFrom,"-") != 0)
			{
				$base_query .= "AND wine.year >= ".$search_rangeFrom." ";
				$criteria .= "Start Year : <i>'".$search_rangeFrom."'</i><br/> ";
			}
			if(strcmp($search_rangeTo,"-") != 0)
			{
				$base_query .= "AND wine.year <= ".$search_rangeTo." ";
				$criteria .= "End Year : <i>'".$search_rangeFrom."'</i><br/> ";
			}
			if(strcmp($search_minNoStock,"") != 0)
			{
				$base_query .= "AND inventory.on_hand >= ".$search_minNoStock." ";
				$criteria .= "Min No Of Wines on hand : <i>'".$search_minNoStock."'</i><br/> ";
			}
			if(strcmp($search_minNoOrder,"" != 0))
			{
				$criteria .= "Min No Of Wines ordered : <i>'".$search_minNoOrder."'</i> ";
			}
			$criteria .= "</font><br>";
			$base_query .= "ORDER BY wine.wine_name, wine.year ASC ";
			$smarty->assign('baseQuery',$base_query);
			$smarty->assign('searchCriteria',$criteria);
			
			try 
			{
				//initialize PDO-SQL driver with defined parameter
				$db = new PDO(DB_TYPE. ":host=" . DB_HOST . ";port=" . DB_PORT . ";dbname=" . DB_NAME, DB_USER, DB_PW);
				
				$data = array();
				$data2;
				$results = $db->query($base_query);//populate data from PDO driver with incoming query
				
				$totalRows = 0;
				
				foreach ($results as $row) 
				{
					$totalRows++;
					$id = $row['wine_id'];
					$toBePrinted = printQuery("count_stock_order" , $id, $connection, $search_state, $db);
					if($search_minNoOrder <= $quantity || !strcmp($search_minNoOrder,""))
					{
						$data2['wine_name'] = $row['wine_name'];
						$data2['year'] = $row['year'];
						$data2['variety'] = $row['variety'];
						$data2['winery_name'] = $row['winery_name'];
						$data2['region_name'] = $row['region_name'];
						$data2['cost'] = $row['cost'];
						$data2['on_hand'] = $row['on_hand'];
						$data2['totalStockSold'] = $toBePrinted;
						$toBePrinted2 = printQuery("count_customer", $id, $connection, $search_state, $db);
						$data2['totalCust'] = $toBePrinted2;
						array_push ($data, $data2);//push the dataset into array
					}
					else
					{
						$totalRows--;
					}
				}
				
				$db = null; // close the database connection
				
				$smarty->assign('result',$totalRows);
			} 
			catch(PDOException $e) 
			{
				echo $e->getMessage();
			}
			if(isset($_SESSION['isActive']) && $_SESSION['isActive'] == true)//if the session is active,
			{
				
				if(isset($_SESSION['history']) == false)//if there is no history, make the current search as the first one
				{
					$bigArray['1'] = $data;
					$_SESSION['history'] = $bigArray;
				}
				else//else, appends the new data to new array and add it to session
				{
					$bigArray = $_SESSION['history'];
					$bigArray[count($bigArray)+1] = $data;
					$_SESSION['history'] = $bigArray;
				}
				$smarty->assign('notice',generateNoticeSession());
			}
			$smarty->assign('queryResult',$data);
			$smarty->display('resultModule.tpl');

		}

	}
	/*
		Function to generate notification of the session being online or not
	*/
	function generateNoticeSession()
	{
		$notice .= "SESSION IS ACTIVE<br>";
		
		if(isset($_SESSION['history']) == false)
		{
			$notice .= "Session is Empty<br>";
		}
		else
		{
			$bigArray = $_SESSION['history'];
			for($i = 1; $i<=count($bigArray) ; $i++)
			{
				$notice .= "<a href = 'resultModule.php?historyIndex=".$i."&isDisplayingHistory=true'>History No.".$i."</a>, ";
				if ($i % 5 == 0)
				{
					$notice .= "<br>";
				}
			}
		}
		$notice .= "<a href='searchScreen.php?destroySession=true'>Destroy This Session!</a>";
		return $notice;
	}
	
	/*
		Function to Print Query by receiving whether printing count stock order or count customer by providing the wine id also
	*/
	function printQuery($choice, $id, $connection, $search_state, $db)
	{
		$GLOBALS['quantity'] = 0;
		$toBePrinted = "";
		$query = "";
		if($choice == "count_stock_order")
		{
			$query = "SELECT SUM(i.qty) AS quantity FROM items i, customer c where c.cust_id = i.cust_id AND i.wine_id =".$id;
		}
		else if($choice == "count_customer")
		{
			$query = "SELECT COUNT(i.cust_id) AS num_of_customer FROM items i, customer c where c.cust_id = i.cust_id AND i.wine_id =".$id;
		}
		if(strcmp($search_state,"-") != 0 && strcmp($search_state,"ALL") != 0)
		{
			$query .= " AND c.state = '".$search_state."' ";
		}
		$result = $db->query($query);
		foreach($result as $row)
		{
			if($choice == "count_stock_order")
			{
				if($row['quantity'] == 0)
				{
					$toBePrinted .= "NO WINE";
				}
				else
				{
					$GLOBALS['quantity'] = $row['quantity'];
					$toBePrinted .= $row['quantity'];

				}
			}
			else if($choice == "count_customer")
			{
				if($row['num_of_customer'] == 0)
				{
					$toBePrinted .= "NO CUSTOMER";
				}
				else
				{
					$toBePrinted .= $row['num_of_customer'];
				}
			}
		}
		return $toBePrinted;
	}
	
	/*
		Function to Validate whether the inputted argument is an integer or not
	*/
	function isInteger($s)
	{   
		for ($index=0;$index<strlen($s);$index++) 
		{ 
			if(!isNumber($s[$index])) 
				return false;
		}
		return true;
	}
	/*
		Function to checks per character using regular expression of a Number
	*/
	function isNumber($c) 
	{ 
		$isNum = preg_match('/[0-9]/', $c);
		return $isNum; 
	} 
?>